USE [db-IPC01]
GO
/****** Object:  StoredProcedure [dbo].[pr__SYS_MakeInsertRecordProc]    Script Date: 01/31/2008 13:52:17 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pr__SYS_MakeInsertRecordProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pr__SYS_MakeInsertRecordProc]
GO
/****** Object:  StoredProcedure [dbo].[pr__SYS_MakeSelectRecordProc]    Script Date: 01/31/2008 13:52:18 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pr__SYS_MakeSelectRecordProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pr__SYS_MakeSelectRecordProc]
GO
/****** Object:  StoredProcedure [dbo].[pr__SYS_MakeUpdateRecordProc]    Script Date: 01/31/2008 13:52:18 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pr__SYS_MakeUpdateRecordProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pr__SYS_MakeUpdateRecordProc]
GO
/****** Object:  StoredProcedure [dbo].[prOrm_TableColumns_Select]    Script Date: 01/31/2008 13:52:18 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prOrm_TableColumns_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[prOrm_TableColumns_Select]
GO
/****** Object:  StoredProcedure [dbo].[prOrm_ForeignKeys_Select]    Script Date: 01/31/2008 13:52:18 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prOrm_ForeignKeys_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[prOrm_ForeignKeys_Select]
GO
/****** Object:  StoredProcedure [dbo].[pr__SYS_PrintAllCRUDProcs]    Script Date: 01/31/2008 13:52:18 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pr__SYS_PrintAllCRUDProcs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pr__SYS_PrintAllCRUDProcs]
GO
/****** Object:  StoredProcedure [dbo].[pr__SYS_MakeDeleteRecordProc]    Script Date: 01/31/2008 13:52:17 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pr__SYS_MakeDeleteRecordProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pr__SYS_MakeDeleteRecordProc]
GO
/****** Object:  StoredProcedure [dbo].[pr__SYS_MakeSelectAllRecordProc]    Script Date: 01/31/2008 13:52:18 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pr__SYS_MakeSelectAllRecordProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pr__SYS_MakeSelectAllRecordProc]
GO
/****** Object:  StoredProcedure [dbo].[pr__SYS_MakeCRUDSprocs]    Script Date: 01/31/2008 13:52:17 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pr__SYS_MakeCRUDSprocs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pr__SYS_MakeCRUDSprocs]
GO
/****** Object:  UserDefinedFunction [dbo].[fnTableColumnInfo]    Script Date: 01/31/2008 13:52:19 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnTableColumnInfo]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fnTableColumnInfo]
GO
/****** Object:  UserDefinedFunction [dbo].[fnTableHasPrimaryKey]    Script Date: 01/31/2008 13:52:18 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnTableHasPrimaryKey]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fnTableHasPrimaryKey]
GO
/****** Object:  UserDefinedFunction [dbo].[fnSys_GetCSharpTypeFromXType]    Script Date: 01/31/2008 13:52:19 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnSys_GetCSharpTypeFromXType]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fnSys_GetCSharpTypeFromXType]
GO
/****** Object:  UserDefinedFunction [dbo].[fnSys_CamelCaseWord]    Script Date: 01/31/2008 13:52:19 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnSys_CamelCaseWord]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fnSys_CamelCaseWord]
GO
/****** Object:  UserDefinedFunction [dbo].[fnSys_RemoveFKPrefix]    Script Date: 01/31/2008 13:52:19 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnSys_RemoveFKPrefix]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fnSys_RemoveFKPrefix]
GO
/****** Object:  UserDefinedFunction [dbo].[fnOrm_IsColumnForeignKey]    Script Date: 01/31/2008 13:52:19 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnOrm_IsColumnForeignKey]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fnOrm_IsColumnForeignKey]
GO
/****** Object:  View [dbo].[VFK]    Script Date: 01/31/2008 13:52:20 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[VFK]'))
DROP VIEW [dbo].[VFK]
GO
/****** Object:  UserDefinedFunction [dbo].[fnColumnDefault]    Script Date: 01/31/2008 13:52:20 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnColumnDefault]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fnColumnDefault]
GO
/****** Object:  UserDefinedFunction [dbo].[fnIsColumnPrimaryKey]    Script Date: 01/31/2008 13:52:20 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnIsColumnPrimaryKey]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fnIsColumnPrimaryKey]
GO
/****** Object:  UserDefinedFunction [dbo].[fnCleanDefaultValue]    Script Date: 01/31/2008 13:52:20 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnCleanDefaultValue]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fnCleanDefaultValue]
GO
/****** Object:  UserDefinedFunction [dbo].[fnSys_GetCSharpTypeFromXType]    Script Date: 01/31/2008 13:52:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnSys_GetCSharpTypeFromXType]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[fnSys_GetCSharpTypeFromXType] (@XType tinyInt)
	RETURNS VARCHAR(20)
AS
BEGIN
	DECLARE @ReturnType VARCHAR(20)
	SELECT @ReturnType = ''#NOT FOUND#''	

	IF @XType = 127  --bigint
	BEGIN
		SELECT @ReturnType = ''int''
	END

	IF @XType = 173  --binary
	BEGIN
		 SELECT @ReturnType = ''#UKNOWN# binary''
	END
	
	IF @XType = 104  --bit
	BEGIN
		 SELECT @ReturnType = ''bool''
	END
	
	IF @XType = 175  --char
	BEGIN
		 SELECT @ReturnType = ''char''
	END
	
	IF @XType = 61  --datetime
	BEGIN
		 SELECT @ReturnType = ''DateTime''
	END
	
	IF @XType = 106  --decimal
	BEGIN
		 SELECT @ReturnType = ''Decimal''
	END
	
	IF @XType = 62  --float
	BEGIN
		 SELECT @ReturnType = ''float''
	END
	
	IF @XType = 34  --image
	BEGIN
		 SELECT @ReturnType = '' image''
	END
	
	IF @XType = 56  --int
	BEGIN
		 SELECT @ReturnType = ''int''
	END
	
	IF @XType = 60  --money
	BEGIN
		 SELECT @ReturnType = ''decimal''
	END
	
	IF @XType = 239  --nchar
	BEGIN
		 SELECT @ReturnType = ''char''
	END
	
	IF @XType = 99  --ntext
	BEGIN
		 SELECT @ReturnType = ''string''
	END
	
	IF @XType = 108  --numeric
	BEGIN
		 SELECT @ReturnType = ''int''
	END
	
	IF @XType = 231  --nvarchar
	BEGIN
		 SELECT @ReturnType = ''string''
	END
	
	IF @XType = 59  --real
	BEGIN
		 SELECT @ReturnType = ''float''
	END
	
	IF @XType = 58  --smalldatetime
	BEGIN
		 SELECT @ReturnType = ''DateTime''
	END

	IF @XType = 52  --smallint
	BEGIN
		 SELECT @ReturnType = ''int''
	END
	
	IF @XType = 122  --smallmoney
	BEGIN
		 SELECT @ReturnType = ''decimal''
	END
	
	IF @XType = 98  --sql_variant
	BEGIN
		 SELECT @ReturnType = ''#UKNOWN# sql_variant''
	END
	
	
	IF @XType = 35  --text
	BEGIN
		 SELECT @ReturnType = ''string''
	END
	
	IF @XType = 189  --timestamp
	BEGIN
		 SELECT @ReturnType = ''DateTime''
	END
	
	IF @XType = 48  --tinyint
	BEGIN
		 SELECT @ReturnType = ''int''
	END
	
	IF @XType = 36  --uniqueidentifier
	BEGIN
		 SELECT @ReturnType = ''Guid''
	END
	
	IF @XType = 165  --varbinary
	BEGIN
		 SELECT @ReturnType = ''#UKNOWN# varbinary''
	END
	
	IF @XType = 167  --varchar
	BEGIN
		 SELECT @ReturnType = ''string''
	END

	RETURN @ReturnType

END' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[fnSys_CamelCaseWord]    Script Date: 01/31/2008 13:52:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnSys_CamelCaseWord]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE  FUNCTION [dbo].[fnSys_CamelCaseWord] (@Text NVARCHAR(255))
	RETURNS NVARCHAR(255)
AS
BEGIN
	DECLARE @ReturnText NVARCHAR(255)
	SELECT @ReturnText =  LOWER(LEFT(@Text, 1))+ RIGHT(@Text, LEN(@Text) -1)
	
	RETURN @ReturnText
END' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[fnSys_RemoveFKPrefix]    Script Date: 01/31/2008 13:52:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnSys_RemoveFKPrefix]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE  FUNCTION [dbo].[fnSys_RemoveFKPrefix] (@Text NVARCHAR(255))
	RETURNS NVARCHAR(255)
AS
BEGIN
	DECLARE @ReturnText NVARCHAR(255)
	SELECT @ReturnText = REPLACE(@Text, ''FK_'', '''')
	
	RETURN @ReturnText
END' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[fnIsColumnPrimaryKey]    Script Date: 01/31/2008 13:52:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnIsColumnPrimaryKey]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE   FUNCTION [dbo].[fnIsColumnPrimaryKey](@sTableName varchar(128), @nColumnName varchar(128))
RETURNS bit
AS
BEGIN
	DECLARE @nTableID int,
		@nIndexID int,
		@i int
	
	SET 	@nTableID = OBJECT_ID(@sTableName)
	
	SELECT 	@nIndexID = indid
	FROM 	sysindexes
	WHERE 	id = @nTableID
	 AND 	indid BETWEEN 1 And 254 
	 AND 	(status & 2048) = 2048
	
	IF @nIndexID Is Null
		RETURN 0
	
	IF @nColumnName IN
		(SELECT sc.[name]
		FROM 	sysindexkeys sik
			INNER JOIN syscolumns sc ON sik.id = sc.id AND sik.colid = sc.colid
		WHERE 	sik.id = @nTableID
		 AND 	sik.indid = @nIndexID)
	 BEGIN
		RETURN 1
	 END


	RETURN 0
END
' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[fnCleanDefaultValue]    Script Date: 01/31/2008 13:52:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnCleanDefaultValue]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'--------------------------------------------------------------------------------------------------------

CREATE FUNCTION [dbo].[fnCleanDefaultValue](@sDefaultValue varchar(4000))
RETURNS varchar(4000)
AS
BEGIN
	RETURN SubString(@sDefaultValue, 2, DataLength(@sDefaultValue)-2)
END
' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[fnTableHasPrimaryKey]    Script Date: 01/31/2008 13:52:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnTableHasPrimaryKey]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'--------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[fnTableHasPrimaryKey](@sTableName varchar(128))
RETURNS bit
AS
BEGIN
	DECLARE @nTableID int,
		@nIndexID int
	
	SET 	@nTableID = OBJECT_ID(@sTableName)
	
	SELECT 	@nIndexID = indid
	FROM 	sysindexes
	WHERE 	id = @nTableID
	 AND 	indid BETWEEN 1 And 254 
	 AND 	(status & 2048) = 2048
	
	IF @nIndexID IS NOT Null
		RETURN 1
	
	RETURN 0
END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[pr__SYS_PrintAllCRUDProcs]    Script Date: 01/31/2008 13:52:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pr__SYS_PrintAllCRUDProcs]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[pr__SYS_PrintAllCRUDProcs]

AS
BEGIN
	SELECT 
		''EXEC pr__SYS_MakeDeleteRecordProc ''''''+ [name] +'''''', 1'' + CHAR(10) +
		''EXEC pr__SYS_MakeInsertRecordProc ''''''+ [name] +'''''', 1'' + CHAR(10) +
		''EXEC pr__SYS_MakeSelectRecordProc ''''''+ [name] +'''''', 1'' + CHAR(10) +
		''EXEC pr__SYS_MakeUpdateRecordProc ''''''+ [name] +'''''', 1'' + CHAR(10) 
	FROM SYSObjects 
	WHERE 
		XType = ''U'' AND 
		[name] != ''sysdiagrams'' 
	ORDER BY [name]
END
' 
END
GO
/****** Object:  View [dbo].[VFK]    Script Date: 01/31/2008 13:52:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[VFK]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[VFK] AS 
--Author: N.Raghavendra 
--Created on: 7-Apr-2003 
--Version: 1.0 
SELECT 
--Name of the FOREIGN KEY constraint 
SO3.NAME FK_NAME, 
--Unique ID of FOREIGN KEY constraint 
SO3.ID FK_ID, 
-- Owner of the table with the FOREIGN KEY constraint 
SU.NAME TABLE_OWNER, 
--Name of the table with the FOREIGN KEY constraint 
SO.NAME TABLE_NAME, 
--Object identification number. 
SO.ID TABLE_ID, 
--Name of the Column with the FOREIGN KEY constraint 
SC.NAME COLUMN_NAME, 
--Position of the column in the FOREIGN KEY constraint 
SC.COLID COLID, 
--Owner of the table referenced in the FOREIGN KEY constraint 
SU2.NAME REF_TABLE_OWNER, 
--Name of the table referenced in the FOREIGN KEY constraint 
SO2.NAME REF_TABLE_NAME, 
--Object identification number of the table referenced in the FOREIGN KEY constraint. 
SO2.ID REF_TABLE_ID, 
--Name of the column referenced in the FOREIGN KEY constraint 
SC2.NAME REF_COLUMN_NAME, 
--Position of the column in the reference column list 
SC2.COLID REF_TABLE_COLID 
 
FROM SYSFOREIGNKEYS SYSFK 
--Foreign Key Constraint - Table info 
INNER JOIN (SELECT UID, ID, NAME FROM SYSOBJECTS WHERE XTYPE = ''U'') SO 
ON SYSFK.FKEYID = SO.ID 
--Referenced in the FOREIGN KEY constraint - Table info 
INNER JOIN (SELECT UID, ID, NAME FROM SYSOBJECTS WHERE XTYPE = ''U'') SO2 
ON SYSFK.RKEYID = SO2.ID 
--Foreign Key Constraint - Column info 
INNER JOIN (select ID, COLID, NAME FROM SYSCOLUMNS) SC ON SYSFK.FKEYID = 
SC.ID AND SYSFK.FKEY = SC.COLID 
--Referenced in the FOREIGN KEY constraint - Column info 
INNER JOIN (select ID, COLID, NAME FROM SYSCOLUMNS) SC2 ON SYSFK.RKEYID 
= SC2.ID AND SYSFK.RKEY = SC2.COLID 
--Name and ID of the FOREIGN KEY constraint 
INNER JOIN (SELECT ID, NAME FROM SYSOBJECTS) SO3 ON SYSFK.CONSTID = 
SO3.ID 
--Foreign Key Constraint - Owner info 
INNER JOIN SYSUSERS SU ON SO.UID = SU.UID 
--Referenced in the FOREIGN KEY constraint - Owner info 
INNER JOIN SYSUSERS SU2 ON SO2.UID = SU2.UID 


--

'
GO
/****** Object:  StoredProcedure [dbo].[pr__SYS_MakeCRUDSprocs]    Script Date: 01/31/2008 13:52:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pr__SYS_MakeCRUDSprocs]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[pr__SYS_MakeCRUDSprocs]
	@TableName AS NVARCHAR(2000)
AS
BEGIN
DECLARE @execStr VARCHAR(4000)
SET @execStr = 
	''EXEC dbo.pr__SYS_MakeInsertRecordProc ''''''+@TableName + '''''', 1'' + CHAR(13) + 
	''EXEC dbo.pr__SYS_MakeSelectRecordProc ''''''+@TableName + '''''', 1'' + CHAR(13) +
	''EXEC dbo.pr__SYS_MakeUpdateRecordProc ''''''+@TableName + '''''', 1'' + CHAR(13) +
	''EXEC dbo.pr__SYS_MakeDeleteRecordProc ''''''+@TableName + '''''', 1'' + CHAR(13) +
	''EXEC dbo.pr__SYS_MakeSelectAllRecordProc ''''''+@TableName + '''''', 1'' + CHAR(13)
	


PRINT @execStr;

EXEC (@execStr)
END
 ' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[fnColumnDefault]    Script Date: 01/31/2008 13:52:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnColumnDefault]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'--------------------------------------------------------------------------------------------------------

CREATE FUNCTION [dbo].[fnColumnDefault](@sTableName varchar(128), @sColumnName varchar(128))
RETURNS varchar(4000)
AS
BEGIN
	DECLARE @sDefaultValue varchar(4000)

	SELECT	@sDefaultValue = dbo.fnCleanDefaultValue(COLUMN_DEFAULT)
	FROM	INFORMATION_SCHEMA.COLUMNS
	WHERE	TABLE_NAME = @sTableName
	 AND 	COLUMN_NAME = @sColumnName

	RETURN 	@sDefaultValue

END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[pr__SYS_MakeDeleteRecordProc]    Script Date: 01/31/2008 13:52:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pr__SYS_MakeDeleteRecordProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'--------------------------------------------------------------------------------------------------------
CREATE      PROC [dbo].[pr__SYS_MakeDeleteRecordProc]
	@sTableName varchar(128),
	@bExecute bit = 0
AS

IF dbo.fnTableHasPrimaryKey(@sTableName) = 0
 BEGIN
	RAISERROR (''Procedure cannot be created on a table with no primary key.'', 10, 1)
	RETURN
 END

DECLARE	@sProcText varchar(8000),
	@sKeyFields varchar(2000),
	@sWhereClause varchar(2000),
	@sColumnName varchar(128),
	@nColumnID smallint,
	@bPrimaryKeyColumn bit,
	@nAlternateType int,
	@nColumnLength int,
	@nColumnPrecision int,
	@nColumnScale int,
	@IsNullable bit, 
	@IsIdentity int,
	@sTypeName varchar(128),
	@sDefaultValue varchar(4000),
	@sCRLF char(2),
	@sTAB char(1)

SET	@sTAB = char(9)
SET 	@sCRLF = char(13) + char(10)

SET 	@sProcText = ''''
SET 	@sKeyFields = ''''
SET	@sWhereClause = ''''

SET 	@sProcText = @sProcText + ''IF EXISTS(SELECT * FROM sysobjects WHERE name = ''''prApp_'' + @sTableName + ''_Delete'''')'' + @sCRLF
SET 	@sProcText = @sProcText + @sTAB + ''DROP PROC prApp_'' + @sTableName + ''_Delete'' + @sCRLF
IF @bExecute = 0
	SET 	@sProcText = @sProcText + ''GO'' + @sCRLF

SET 	@sProcText = @sProcText + @sCRLF

PRINT @sProcText

IF @bExecute = 1 
	EXEC (@sProcText)

SET 	@sProcText = ''''
SET 	@sProcText = @sProcText + ''----------------------------------------------------------------------------'' + @sCRLF
SET 	@sProcText = @sProcText + ''-- Delete a single record from '' + @sTableName + @sCRLF
SET 	@sProcText = @sProcText + ''----------------------------------------------------------------------------'' + @sCRLF
SET 	@sProcText = @sProcText + ''CREATE PROC prApp_'' + @sTableName + ''_Delete'' + @sCRLF

DECLARE crKeyFields cursor for
	SELECT	*
	FROM	dbo.fnTableColumnInfo(@sTableName)
	ORDER BY 2

OPEN crKeyFields

FETCH 	NEXT 
FROM 	crKeyFields 
INTO 	@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, 
	@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, 
	@IsIdentity, @sTypeName, @sDefaultValue
				
WHILE (@@FETCH_STATUS = 0)
 BEGIN

	IF (@bPrimaryKeyColumn = 1)
	 BEGIN
		IF (@sKeyFields <> '''')
			SET @sKeyFields = @sKeyFields + '','' + @sCRLF 
	
		SET @sKeyFields = @sKeyFields + @sTAB + ''@'' + @sColumnName + '' '' + @sTypeName

		IF (@nAlternateType = 2) --decimal, numeric
			SET @sKeyFields =  @sKeyFields + ''('' + CAST(@nColumnPrecision AS varchar(3)) + '', '' 
					+ CAST(@nColumnScale AS varchar(3)) + '')''
	
		ELSE IF (@nAlternateType = 1) --character and binary
			SET @sKeyFields =  @sKeyFields + ''('' + CAST(@nColumnLength AS varchar(4)) +  '')''
	
		IF (@sWhereClause = '''')
			SET @sWhereClause = @sWhereClause + ''WHERE '' 
		ELSE
			SET @sWhereClause = @sWhereClause + '' AND '' 

		SET @sWhereClause = @sWhereClause + @sTAB + @sColumnName  + '' = @'' + @sColumnName + @sCRLF 
	 END

	FETCH 	NEXT 
	FROM 	crKeyFields 
	INTO 	@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, 
		@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, 
		@IsIdentity, @sTypeName, @sDefaultValue
 END

CLOSE crKeyFields
DEALLOCATE crKeyFields

SET 	@sProcText = @sProcText + @sKeyFields + @sCRLF
SET 	@sProcText = @sProcText + ''AS'' + @sCRLF
SET 	@sProcText = @sProcText + @sCRLF
SET 	@sProcText = @sProcText + ''DELETE	'' + @sTableName + @sCRLF
SET 	@sProcText = @sProcText + @sWhereClause
SET 	@sProcText = @sProcText + @sCRLF
IF @bExecute = 0
	SET 	@sProcText = @sProcText + ''GO'' + @sCRLF


PRINT @sProcText

IF @bExecute = 1 
	EXEC (@sProcText)
' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[fnOrm_IsColumnForeignKey]    Script Date: 01/31/2008 13:52:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnOrm_IsColumnForeignKey]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Lewis Barclay
-- Create date: 04/04/2007
-- Description:	Returns 1 if the column is a foreing key and 0 if not
-- =============================================
CREATE FUNCTION [dbo].[fnOrm_IsColumnForeignKey]
(
	@tableId INT,
	@colId INT
)
RETURNS BIT
AS
BEGIN
	DECLARE @ToReturn BIT 
	SET @ToReturn = 0

	IF EXISTS(
		SELECT * 
		FROM VFK
		WHERE 
			TABLE_ID = @tableId 
		AND	COLID = @colId	
	)
	SET @ToReturn = 1

	RETURN @ToReturn

END
' 
END
GO
/****** Object:  StoredProcedure [dbo].[prOrm_ForeignKeys_Select]    Script Date: 01/31/2008 13:52:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prOrm_ForeignKeys_Select]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Lewis Barclay
-- Create date: 31/03/2007
-- Description:	Gets a list of foreign keys for a table
-- =============================================
CREATE PROCEDURE [dbo].[prOrm_ForeignKeys_Select]
	@TableName nvarchar(255)
AS
BEGIN
	SELECT 
		FK_NAME,
		FK_ID,
		TABLE_NAME,
		COLUMN_NAME,
		REF_TABLE_NAME,
		REF_COLUMN_NAME 
	FROM VFK 
	WHERE 
		[TABLE_NAME] = @TableName
END



--

' 
END
GO
/****** Object:  StoredProcedure [dbo].[prOrm_TableColumns_Select]    Script Date: 01/31/2008 13:52:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prOrm_TableColumns_Select]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Lewis Barclay
-- Create date: 31/03/2007
-- Description:	Gets a list of columsn fo a table
-- =============================================
CREATE PROCEDURE [dbo].[prOrm_TableColumns_Select]
	@TableName nvarchar(255)
AS
BEGIN
	SELECT 	
		sc.name,
		dbo.fnSys_GetCSharpTypeFromXType(CAST(sc.xType AS TinyInt)) [Type],
		dbo.fnSys_RemoveFKPrefix(sc.[Name]) [FormattedNameNonCase],
		dbo.fnSys_CamelCaseWord(dbo.fnSys_RemoveFKPrefix(sc.[Name])) [FormattedName],
		dbo.fnOrm_IsColumnForeignKey(sc.id, sc.colId) [ForeignKey],
		sc.isnullable
	FROM sysObjects so
	INNER JOIN sysColumns sc
		ON sc.[id] = so.[id]
	WHERE so.[Name] = @TableName
	ORDER BY colid

END' 
END
GO
/****** Object:  UserDefinedFunction [dbo].[fnTableColumnInfo]    Script Date: 01/31/2008 13:52:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnTableColumnInfo]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE       FUNCTION [dbo].[fnTableColumnInfo](@sTableName varchar(128))
RETURNS TABLE
AS
	RETURN
	SELECT	c.name AS sColumnName,
		c.colid AS nColumnID,
		dbo.fnIsColumnPrimaryKey(@sTableName, c.name) AS bPrimaryKeyColumn,
		CASE 	WHEN t.name IN (''char'', ''varchar'', ''binary'', ''varbinary'', ''nchar'', ''nvarchar'') THEN 1
			WHEN t.name IN (''decimal'', ''numeric'') THEN 2
			ELSE 0
		END AS nAlternateType,
		c.length AS nColumnLength,
		c.prec AS nColumnPrecision,
		c.scale AS nColumnScale, 
		c.IsNullable, 
		SIGN(c.status & 128) AS IsIdentity,
		t.name as sTypeName,
		dbo.fnColumnDefault(@sTableName, c.name) AS sDefaultValue
	FROM	syscolumns c 
		INNER JOIN systypes t ON c.xtype = t.xtype and c.usertype = t.usertype
	WHERE	c.id = OBJECT_ID(@sTableName)
' 
END
GO
/****** Object:  StoredProcedure [dbo].[pr__SYS_MakeInsertRecordProc]    Script Date: 01/31/2008 13:52:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pr__SYS_MakeInsertRecordProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--------------------------------------------------------------------------------------------------------

CREATE       PROC [dbo].[pr__SYS_MakeInsertRecordProc]
	@sTableName varchar(128),
	@bExecute bit = 0
AS
IF dbo.fnTableHasPrimaryKey(@sTableName) = 0
 BEGIN
	RAISERROR (''Procedure cannot be created on a table with no primary key.'', 10, 1)
	RETURN
 END

DECLARE	@sProcText varchar(8000),
	@sKeyFields varchar(2000),
	@sAllFields varchar(2000),
	@sAllParams varchar(2000),
	@sWhereClause varchar(2000),
	@sColumnName varchar(128),
	@nColumnID smallint,
	@bPrimaryKeyColumn bit,
	@nAlternateType int,
	@nColumnLength int,
	@nColumnPrecision int,
	@nColumnScale int,
	@IsNullable bit, 
	@IsIdentity int,
	@HasIdentity int,
	@sTypeName varchar(128),
	@sDefaultValue varchar(4000),
	@sCRLF char(2),
	@sTAB char(1)

SET 	@HasIdentity = 0
SET	@sTAB = char(9)
SET 	@sCRLF = char(13) + char(10)
SET 	@sProcText = ''''
SET 	@sKeyFields = ''''
SET	@sAllFields = ''''
SET	@sWhereClause = ''''
SET	@sAllParams  = ''''

SET 	@sProcText = @sProcText + ''IF EXISTS(SELECT * FROM sysobjects WHERE name = ''''prApp_'' + @sTableName + ''_Insert'''')'' + @sCRLF
SET 	@sProcText = @sProcText + @sTAB + ''DROP PROC prApp_'' + @sTableName + ''_Insert'' + @sCRLF
IF @bExecute = 0
	SET 	@sProcText = @sProcText + ''GO'' + @sCRLF

SET 	@sProcText = @sProcText + @sCRLF

PRINT @sProcText

IF @bExecute = 1 
	EXEC (@sProcText)

SET 	@sProcText = ''''
SET 	@sProcText = @sProcText + ''----------------------------------------------------------------------------'' + @sCRLF
SET 	@sProcText = @sProcText + ''-- Insert a single record into '' + @sTableName + @sCRLF
SET 	@sProcText = @sProcText + ''----------------------------------------------------------------------------'' + @sCRLF
SET 	@sProcText = @sProcText + ''CREATE PROC prApp_'' + @sTableName + ''_Insert'' + @sCRLF

DECLARE crKeyFields cursor for
	SELECT	*
	FROM	dbo.fnTableColumnInfo(@sTableName)
	ORDER BY 2

OPEN crKeyFields


FETCH 	NEXT 
FROM 	crKeyFields 
INTO 	@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, 
	@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, 
	@IsIdentity, @sTypeName, @sDefaultValue
				
WHILE (@@FETCH_STATUS = 0)
 BEGIN
	IF (@IsIdentity = 0)
	 BEGIN
		IF (@sKeyFields <> '''')
			SET @sKeyFields = @sKeyFields + '','' + @sCRLF 

		SET @sKeyFields = @sKeyFields + @sTAB + ''@'' + @sColumnName + '' '' + @sTypeName

		IF (@sAllFields <> '''')
		 BEGIN
			SET @sAllParams = @sAllParams + '', ''
			SET @sAllFields = @sAllFields + '', ''
		 END

		IF (@sTypeName = ''timestamp'')
			SET @sAllParams = @sAllParams + ''NULL''
		ELSE IF (@sDefaultValue IS NOT NULL)
			SET @sAllParams = @sAllParams + ''COALESCE(@'' + @sColumnName + '', '' + @sDefaultValue + '')''
		ELSE
			SET @sAllParams = @sAllParams + ''@'' + @sColumnName 

		SET @sAllFields = @sAllFields + ''[''+@sColumnName+'']'' 

	 END
	ELSE
	 BEGIN
		SET @HasIdentity = 1
	 END

	IF (@nAlternateType = 2) --decimal, numeric
		SET @sKeyFields =  @sKeyFields + ''('' + CAST(@nColumnPrecision AS varchar(3)) + '', '' 
				+ CAST(@nColumnScale AS varchar(3)) + '')''

	ELSE IF (@nAlternateType = 1) --character and binary
		SET @sKeyFields =  @sKeyFields + ''('' + CAST(@nColumnLength AS varchar(4)) +  '')''

	IF (@IsIdentity = 0)
	 BEGIN
		IF (@sDefaultValue IS NOT NULL) OR (@IsNullable = 1) OR (@sTypeName = ''timestamp'')
			SET @sKeyFields = @sKeyFields + '' = NULL''
	 END

	FETCH 	NEXT 
	FROM 	crKeyFields 
	INTO 	@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, 
		@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, 
		@IsIdentity, @sTypeName, @sDefaultValue
 END

CLOSE crKeyFields
DEALLOCATE crKeyFields

SET 	@sProcText = @sProcText + @sKeyFields + @sCRLF
SET 	@sProcText = @sProcText + ''AS'' + @sCRLF
SET 	@sProcText = @sProcText + @sCRLF
SET 	@sProcText = @sProcText + ''INSERT '' + @sTableName + ''('' + @sAllFields + '')'' + @sCRLF
SET 	@sProcText = @sProcText + ''VALUES ('' + @sAllParams + '')'' + @sCRLF
SET 	@sProcText = @sProcText + @sCRLF

IF (@HasIdentity = 1)
 BEGIN
	SET 	@sProcText = @sProcText + ''SELECT * FROM ''+@sTableName+'' WHERE ''+@sTableName+''Id = @@IDENTITY'' + @sCRLF
	SET 	@sProcText = @sProcText + @sCRLF
 END

IF @bExecute = 0
	SET 	@sProcText = @sProcText + ''GO'' + @sCRLF


PRINT @sProcText

IF @bExecute = 1 
	EXEC (@sProcText)
' 
END
GO
/****** Object:  StoredProcedure [dbo].[pr__SYS_MakeSelectRecordProc]    Script Date: 01/31/2008 13:52:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pr__SYS_MakeSelectRecordProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'--------------------------------------------------------------------------------------------------------

CREATE     PROC [dbo].[pr__SYS_MakeSelectRecordProc]
	@sTableName varchar(128),
	@bExecute bit = 0
AS

IF dbo.fnTableHasPrimaryKey(@sTableName) = 0
 BEGIN
	RAISERROR (''Procedure cannot be created on a table with no primary key.'', 10, 1)
	RETURN
 END

DECLARE	@sProcText varchar(8000),
	@sKeyFields varchar(2000),
	@sSelectClause varchar(2000),
	@sWhereClause varchar(2000),
	@sColumnName varchar(128),
	@nColumnID smallint,
	@bPrimaryKeyColumn bit,
	@nAlternateType int,
	@nColumnLength int,
	@nColumnPrecision int,
	@nColumnScale int,
	@IsNullable bit, 
	@IsIdentity int,
	@sTypeName varchar(128),
	@sDefaultValue varchar(4000),
	@sCRLF char(2),
	@sTAB char(1)

SET	@sTAB = char(9)
SET 	@sCRLF = char(13) + char(10)

SET 	@sProcText = ''''
SET 	@sKeyFields = ''''
SET	@sSelectClause = ''''
SET	@sWhereClause = ''''

SET 	@sProcText = @sProcText + ''IF EXISTS(SELECT * FROM sysobjects WHERE name = ''''prApp_'' + @sTableName + ''_Select'''')'' + @sCRLF
SET 	@sProcText = @sProcText + @sTAB + ''DROP PROC prApp_'' + @sTableName + ''_Select'' + @sCRLF
IF @bExecute = 0
	SET 	@sProcText = @sProcText + ''GO'' + @sCRLF

SET 	@sProcText = @sProcText + @sCRLF

PRINT @sProcText

IF @bExecute = 1 
	EXEC (@sProcText)

SET 	@sProcText = ''''
SET 	@sProcText = @sProcText + ''----------------------------------------------------------------------------'' + @sCRLF
SET 	@sProcText = @sProcText + ''-- Select a single record from '' + @sTableName + @sCRLF
SET 	@sProcText = @sProcText + ''----------------------------------------------------------------------------'' + @sCRLF
SET 	@sProcText = @sProcText + ''CREATE PROC prApp_'' + @sTableName + ''_Select'' + @sCRLF

DECLARE crKeyFields cursor for
	SELECT	*
	FROM	dbo.fnTableColumnInfo(@sTableName)
	ORDER BY 2

OPEN crKeyFields

FETCH 	NEXT 
FROM 	crKeyFields 
INTO 	@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, 
	@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, 
	@IsIdentity, @sTypeName, @sDefaultValue
				
WHILE (@@FETCH_STATUS = 0)
 BEGIN
	IF (@bPrimaryKeyColumn = 1)
	 BEGIN
		IF (@sKeyFields <> '''')
			SET @sKeyFields = @sKeyFields + '','' + @sCRLF 
	
		SET @sKeyFields = @sKeyFields + @sTAB + ''@'' + @sColumnName + '' '' + @sTypeName
	
		IF (@nAlternateType = 2) --decimal, numeric
			SET @sKeyFields =  @sKeyFields + ''('' + CAST(@nColumnPrecision AS varchar(3)) + '', '' 
					+ CAST(@nColumnScale AS varchar(3)) + '')''
	
		ELSE IF (@nAlternateType = 1) --character and binary
			SET @sKeyFields =  @sKeyFields + ''('' + CAST(@nColumnLength AS varchar(4)) +  '')''

		IF (@sWhereClause = '''')
			SET @sWhereClause = @sWhereClause + ''WHERE '' 
		ELSE
			SET @sWhereClause = @sWhereClause + '' AND '' 

		SET @sWhereClause = @sWhereClause + @sTAB + ''[''+@sColumnName+'']''  + '' = @'' + @sColumnName + @sCRLF 
	 END

	IF (@sSelectClause = '''')
		SET @sSelectClause = @sSelectClause + ''SELECT''
	ELSE
		SET @sSelectClause = @sSelectClause + '','' + @sCRLF 

	SET @sSelectClause = @sSelectClause + @sTAB + ''[''+@sColumnName+'']'' 

	FETCH 	NEXT 
	FROM 	crKeyFields 
	INTO 	@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, 
		@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, 
		@IsIdentity, @sTypeName, @sDefaultValue
 END

CLOSE crKeyFields
DEALLOCATE crKeyFields

SET 	@sSelectClause = @sSelectClause + @sCRLF

SET 	@sProcText = @sProcText + @sKeyFields + @sCRLF
SET 	@sProcText = @sProcText + ''AS'' + @sCRLF
SET 	@sProcText = @sProcText + @sCRLF
SET 	@sProcText = @sProcText + @sSelectClause
SET 	@sProcText = @sProcText + ''FROM	'' + @sTableName + @sCRLF
SET 	@sProcText = @sProcText + @sWhereClause
SET 	@sProcText = @sProcText + @sCRLF
IF @bExecute = 0
	SET 	@sProcText = @sProcText + ''GO'' + @sCRLF


PRINT @sProcText

IF @bExecute = 1 
	EXEC (@sProcText)
' 
END
GO
/****** Object:  StoredProcedure [dbo].[pr__SYS_MakeUpdateRecordProc]    Script Date: 01/31/2008 13:52:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pr__SYS_MakeUpdateRecordProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--------------------------------------------------------------------------------------------------------

CREATE         PROC [dbo].[pr__SYS_MakeUpdateRecordProc]
	@sTableName varchar(128),
	@bExecute bit = 0
AS
IF dbo.fnTableHasPrimaryKey(@sTableName) = 0
 BEGIN
	RAISERROR (''Procedure cannot be created on a table with no primary key.'', 10, 1)
	RETURN
 END

DECLARE	@sProcText varchar(8000),
	@sKeyFields varchar(2000),
	@sSetClause varchar(2000),
	@sWhereClause varchar(2000),
	@sColumnName varchar(128),
	@nColumnID smallint,
	@bPrimaryKeyColumn bit,
	@nAlternateType int,
	@nColumnLength int,
	@nColumnPrecision int,
	@nColumnScale int,
	@IsNullable bit, 
	@IsIdentity int,
	@sTypeName varchar(128),
	@sDefaultValue varchar(4000),
	@sCRLF char(2),
	@sTAB char(1)

SET	@sTAB = char(9)
SET 	@sCRLF = char(13) + char(10)

SET 	@sProcText = ''''
SET 	@sKeyFields = ''''
SET	@sSetClause = ''''
SET	@sWhereClause = ''''

SET 	@sProcText = @sProcText + ''IF EXISTS(SELECT * FROM sysobjects WHERE name = ''''prApp_'' + @sTableName + ''_Update'''')'' + @sCRLF
SET 	@sProcText = @sProcText + @sTAB + ''DROP PROC prApp_'' + @sTableName + ''_Update'' + @sCRLF
IF @bExecute = 0
	SET 	@sProcText = @sProcText + ''GO'' + @sCRLF

SET 	@sProcText = @sProcText + @sCRLF

PRINT @sProcText

IF @bExecute = 1 
	EXEC (@sProcText)

SET 	@sProcText = ''''
SET 	@sProcText = @sProcText + ''----------------------------------------------------------------------------'' + @sCRLF
SET 	@sProcText = @sProcText + ''-- Update a single record in '' + @sTableName + @sCRLF
SET 	@sProcText = @sProcText + ''----------------------------------------------------------------------------'' + @sCRLF
SET 	@sProcText = @sProcText + ''CREATE PROC prApp_'' + @sTableName + ''_Update'' + @sCRLF

DECLARE crKeyFields cursor for
	SELECT	*
	FROM	dbo.fnTableColumnInfo(@sTableName)
	ORDER BY 2

OPEN crKeyFields


FETCH 	NEXT 
FROM 	crKeyFields 
INTO 	@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, 
	@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, 
	@IsIdentity, @sTypeName, @sDefaultValue
				
WHILE (@@FETCH_STATUS = 0)
 BEGIN
	IF (@sKeyFields <> '''')
		SET @sKeyFields = @sKeyFields + '','' + @sCRLF 

	SET @sKeyFields = @sKeyFields + @sTAB + ''@'' + @sColumnName + '' '' + @sTypeName

	IF (@nAlternateType = 2) --decimal, numeric
		SET @sKeyFields =  @sKeyFields + ''('' + CAST(@nColumnPrecision AS varchar(3)) + '', '' 
				+ CAST(@nColumnScale AS varchar(3)) + '')''

	ELSE IF (@nAlternateType = 1) --character and binary
		SET @sKeyFields =  @sKeyFields + ''('' + CAST(@nColumnLength AS varchar(4)) +  '')''

	IF (@bPrimaryKeyColumn = 1)
	 BEGIN
		IF (@sWhereClause = '''')
			SET @sWhereClause = @sWhereClause + ''WHERE '' 
		ELSE
			SET @sWhereClause = @sWhereClause + '' AND '' 

		SET @sWhereClause = @sWhereClause + @sTAB + ''[''+@sColumnName+'']''  + '' = @'' + @sColumnName + @sCRLF 
	 END
	ELSE
		IF (@IsIdentity = 0)
		 BEGIN
			IF (@sSetClause = '''')
				SET @sSetClause = @sSetClause + ''SET''
			ELSE
				SET @sSetClause = @sSetClause + '','' + @sCRLF 
			SET @sSetClause = @sSetClause + @sTAB + ''[''+@sColumnName+'']''  + '' = ''
			IF (@sTypeName = ''timestamp'')
				SET @sSetClause = @sSetClause + ''NULL''
			ELSE IF (@sDefaultValue IS NOT NULL)
				SET @sSetClause = @sSetClause + ''COALESCE(@'' + @sColumnName + '', '' + @sDefaultValue + '')''
			ELSE
				SET @sSetClause = @sSetClause + ''@'' + @sColumnName 
		 END

	IF (@IsIdentity = 0)
	 BEGIN
		IF (@IsNullable = 1) OR (@sTypeName = ''timestamp'')
			SET @sKeyFields = @sKeyFields + '' = NULL''
	 END

	FETCH 	NEXT 
	FROM 	crKeyFields 
	INTO 	@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, 
		@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, 
		@IsIdentity, @sTypeName, @sDefaultValue
 END

CLOSE crKeyFields
DEALLOCATE crKeyFields

SET 	@sSetClause = @sSetClause + @sCRLF

SET 	@sProcText = @sProcText + @sKeyFields + @sCRLF
SET 	@sProcText = @sProcText + ''AS'' + @sCRLF
SET 	@sProcText = @sProcText + @sCRLF
SET 	@sProcText = @sProcText + ''UPDATE	'' + @sTableName + @sCRLF
SET 	@sProcText = @sProcText + @sSetClause
SET 	@sProcText = @sProcText + @sWhereClause
SET 	@sProcText = @sProcText + @sCRLF

SET 	@sProcText = @sProcText + ''SELECT * FROM ''+@sTableName+'' WHERE ''+@sTableName+''Id = @''+@sTableName+''Id'' + @sCRLF

IF @bExecute = 0
	SET 	@sProcText = @sProcText + ''GO'' + @sCRLF


PRINT @sProcText

IF @bExecute = 1 
	EXEC (@sProcText)



--------------------------------------------------------------------------------------------------------
' 
END
GO
/****** Object:  StoredProcedure [dbo].[pr__SYS_MakeSelectAllRecordProc]    Script Date: 01/31/2008 13:52:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pr__SYS_MakeSelectAllRecordProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'--------------------------------------------------------------------------------------------------------

CREATE     PROC [dbo].[pr__SYS_MakeSelectAllRecordProc]
	@sTableName varchar(128),
	@bExecute bit = 0
AS

IF dbo.fnTableHasPrimaryKey(@sTableName) = 0
 BEGIN
	RAISERROR (''Procedure cannot be created on a table with no primary key.'', 10, 1)
	RETURN
 END

DECLARE	@sProcText varchar(8000),
	@sKeyFields varchar(2000),
	@sSelectClause varchar(2000),
	@sWhereClause varchar(2000),
	@sColumnName varchar(128),
	@nColumnID smallint,
	@bPrimaryKeyColumn bit,
	@nAlternateType int,
	@nColumnLength int,
	@nColumnPrecision int,
	@nColumnScale int,
	@IsNullable bit, 
	@IsIdentity int,
	@sTypeName varchar(128),
	@sDefaultValue varchar(4000),
	@sCRLF char(2),
	@sTAB char(1)

SET	@sTAB = char(9)
SET 	@sCRLF = char(13) + char(10)

SET 	@sProcText = ''''
SET 	@sKeyFields = ''''
SET	@sSelectClause = ''''
SET	@sWhereClause = ''''

SET 	@sProcText = @sProcText + ''IF EXISTS(SELECT * FROM sysobjects WHERE name = ''''prApp_'' + @sTableName + ''_SelectAll'''')'' + @sCRLF
SET 	@sProcText = @sProcText + @sTAB + ''DROP PROC prApp_'' + @sTableName + ''_SelectAll'' + @sCRLF
IF @bExecute = 0
	SET 	@sProcText = @sProcText + ''GO'' + @sCRLF

SET 	@sProcText = @sProcText + @sCRLF

PRINT @sProcText

IF @bExecute = 1 
	EXEC (@sProcText)

SET 	@sProcText = ''''
SET 	@sProcText = @sProcText + ''----------------------------------------------------------------------------'' + @sCRLF
SET 	@sProcText = @sProcText + ''-- Select all records from '' + @sTableName + @sCRLF
SET 	@sProcText = @sProcText + ''----------------------------------------------------------------------------'' + @sCRLF
SET 	@sProcText = @sProcText + ''CREATE PROC prApp_'' + @sTableName + ''_SelectAll'' + @sCRLF

DECLARE crKeyFields cursor for
	SELECT	*
	FROM	dbo.fnTableColumnInfo(@sTableName)
	ORDER BY 2

OPEN crKeyFields

FETCH 	NEXT 
FROM 	crKeyFields 
INTO 	@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, 
	@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, 
	@IsIdentity, @sTypeName, @sDefaultValue
				
WHILE (@@FETCH_STATUS = 0)
 BEGIN
	IF (@bPrimaryKeyColumn = 1)
	 BEGIN
		IF (@sKeyFields <> '''')
			SET @sKeyFields = @sKeyFields + '','' + @sCRLF 
	
		SET @sKeyFields = @sKeyFields + @sTAB + ''@'' + @sColumnName + '' '' + @sTypeName
	
		IF (@nAlternateType = 2) --decimal, numeric
			SET @sKeyFields =  @sKeyFields + ''('' + CAST(@nColumnPrecision AS varchar(3)) + '', '' 
					+ CAST(@nColumnScale AS varchar(3)) + '')''
	
		ELSE IF (@nAlternateType = 1) --character and binary
			SET @sKeyFields =  @sKeyFields + ''('' + CAST(@nColumnLength AS varchar(4)) +  '')''

--		IF (@sWhereClause = '''')
--			SET @sWhereClause = @sWhereClause + ''WHERE '' 
--		ELSE
--			SET @sWhereClause = @sWhereClause + '' AND '' 
--
--		SET @sWhereClause = @sWhereClause + @sTAB + ''[''+@sColumnName+'']''  + '' = @'' + @sColumnName + @sCRLF 
	 END

	IF (@sSelectClause = '''')
		SET @sSelectClause = @sSelectClause + ''SELECT''
	ELSE
		SET @sSelectClause = @sSelectClause + '','' + @sCRLF 

	SET @sSelectClause = @sSelectClause + @sTAB + ''[''+@sColumnName+'']'' 

	FETCH 	NEXT 
	FROM 	crKeyFields 
	INTO 	@sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, 
		@nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, 
		@IsIdentity, @sTypeName, @sDefaultValue
 END

CLOSE crKeyFields
DEALLOCATE crKeyFields

SET 	@sSelectClause = @sSelectClause + @sCRLF

SET 	@sProcText = @sProcText + @sKeyFields + @sCRLF
SET 	@sProcText = @sProcText + ''AS'' + @sCRLF
SET 	@sProcText = @sProcText + @sCRLF
SET 	@sProcText = @sProcText + @sSelectClause
SET 	@sProcText = @sProcText + ''FROM	'' + @sTableName + @sCRLF
--SET 	@sProcText = @sProcText + @sWhereClause
SET 	@sProcText = @sProcText + @sCRLF
IF @bExecute = 0
	SET 	@sProcText = @sProcText + ''GO'' + @sCRLF


PRINT @sProcText

IF @bExecute = 1 
	EXEC (@sProcText)
' 
END
GO
